package com.example.chamberlainserver.mapper;

import com.example.chamberlainserver.Entry.MaterialStockLog;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 物资库存变更记录数据访问接口
 */
@Mapper
public interface MaterialStockLogMapper {
    
    /**
     * 结果映射
     */
    @Results(id = "stockLogResultMap", value = {
        @Result(property = "id", column = "id", id = true),
        @Result(property = "materialId", column = "material_id"),
        @Result(property = "materialName", column = "material_name"),
        @Result(property = "beforeQuantity", column = "before_quantity"),
        @Result(property = "afterQuantity", column = "after_quantity"),
        @Result(property = "changeQuantity", column = "change_quantity"),
        @Result(property = "operationType", column = "operation_type"),
        @Result(property = "operationReason", column = "operation_reason"),
        @Result(property = "operatorName", column = "operator_name"),
        @Result(property = "operationTime", column = "operation_time"),
        @Result(property = "createTime", column = "create_time")
    })
    @Select("SELECT * FROM material_stock_log WHERE id = #{id}")
    MaterialStockLog getStockLogById(@Param("id") Long id);
    
    /**
     * 添加库存变更记录
     * @param stockLog 库存变更记录
     * @return 影响行数
     */
    @Insert("INSERT INTO material_stock_log (" +
           "material_id, material_name, before_quantity, after_quantity, change_quantity, " +
           "operation_type, operation_reason, operator_name, operation_time, create_time" +
           ") VALUES (" +
           "#{materialId}, #{materialName}, #{beforeQuantity}, #{afterQuantity}, #{changeQuantity}, " +
           "#{operationType}, #{operationReason}, #{operatorName}, #{operationTime}, NOW()" +
           ")")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertStockLog(MaterialStockLog stockLog);
    
    /**
     * 根据物资ID获取库存变更记录
     * @param materialId 物资ID
     * @return 库存变更记录列表
     */
    @ResultMap("stockLogResultMap")
    @Select("SELECT * FROM material_stock_log " +
           "WHERE material_id = #{materialId} " +
           "ORDER BY operation_time DESC")
    List<MaterialStockLog> getStockLogsByMaterialId(@Param("materialId") Long materialId);
    
    /**
     * 根据时间范围获取库存变更记录
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 库存变更记录列表
     */
    @ResultMap("stockLogResultMap")
    @Select("SELECT * FROM material_stock_log " +
           "WHERE operation_time BETWEEN #{startTime} AND #{endTime} " +
           "ORDER BY operation_time ASC")
    List<MaterialStockLog> getStockLogsByTimeRange(
            @Param("startTime") LocalDateTime startTime, 
            @Param("endTime") LocalDateTime endTime);
    
    /**
     * 获取指定日期的库存总量
     * @param date 日期
     * @return 库存总量
     */
    @Select("SELECT SUM(after_quantity) as total_inventory " +
           "FROM (" +
           "  SELECT material_id, MAX(operation_time) as latest_time " +
           "  FROM material_stock_log " +
           "  WHERE DATE(operation_time) <= #{date} " +
           "  GROUP BY material_id" +
           ") as latest_logs " +
           "JOIN material_stock_log msl ON latest_logs.material_id = msl.material_id " +
           "AND latest_logs.latest_time = msl.operation_time")
    Integer getTotalInventoryByDate(@Param("date") LocalDate date);
    
    /**
     * 获取指定时间范围内的每日库存趋势数据
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 每日库存趋势数据
     */
    List<Map<String, Object>> getDailyStockTrend(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    /**
     * 获取指定时间范围内的每周库存趋势数据
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 每周库存趋势数据
     */
    List<Map<String, Object>> getWeeklyStockTrend(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    /**
     * 获取指定时间范围内的每月库存趋势数据
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 每月库存趋势数据
     */
    List<Map<String, Object>> getMonthlyStockTrend(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    /**
     * 获取指定物资在指定时间范围内的库存趋势数据
     * @param materialId 物资ID
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @param period 统计周期（daily, weekly, monthly）
     * @return 物资库存趋势数据
     */
    List<Map<String, Object>> getMaterialStockTrend(
            @Param("materialId") Long materialId, 
            @Param("startDate") LocalDate startDate, 
            @Param("endDate") LocalDate endDate,
            @Param("period") String period);
    
    /**
     * 获取指定日期每个物资的最新库存量
     * @param date 日期
     * @return 物资库存数据
     */
    @Select("SELECT " +
           "  msl.material_id, " +
           "  msl.material_name, " +
           "  msl.after_quantity as current_stock " +
           "FROM material_stock_log msl " +
           "INNER JOIN (" +
           "  SELECT material_id, MAX(operation_time) as latest_time " +
           "  FROM material_stock_log " +
           "  WHERE DATE(operation_time) <= #{date} " +
           "  GROUP BY material_id" +
           ") latest ON msl.material_id = latest.material_id " +
           "  AND msl.operation_time = latest.latest_time")
    List<Map<String, Object>> getMaterialStockByDate(@Param("date") LocalDate date);

}